/**
 * MySQL查询语句生成器（增强安全版）
 */

export const MysqlQueryBuilder = {
  /**
   * 基础查询
   * @param {string} table 表名
   * @param {string[]} fields 查询字段数组
   * @param {object} conditions 条件对象(支持操作符)
   * @param {object} options 选项 {limit, offset, orderBy, groupBy}
   * @returns {string} 完整SQL查询语句
   */
  select: (table, fields = ['*'], conditions = {}, options = {}) => {
    // 安全处理表名和字段
    const safeTable = escapeIdentifier(table);
    const safeFields = fields.map(f => 
      f === '*' ? '*' : escapeIdentifier(f)
    ).join(', ');
    
    let sql = `SELECT ${safeFields} FROM ${safeTable}`;

    // 添加WHERE条件
    if (Object.keys(conditions).length > 0) {
      sql += ` WHERE ${buildWhereClause(conditions)}`;
    }

    // 添加其他选项
    if (options.groupBy) {
      sql += ` GROUP BY ${escapeIdentifierOrList(options.groupBy)}`;
    }
    if (options.orderBy) {
      sql += ` ORDER BY ${escapeOrderBy(options.orderBy)}`;
    }
    if (options.limit) {
      const limit = parseInt(options.limit, 10);
      if (!isNaN(limit) && limit > 0) {
        sql += ` LIMIT ${limit}`;
        if (options.offset) {
          const offset = parseInt(options.offset, 10);
          if (!isNaN(offset) && offset >= 0) {
            sql += ` OFFSET ${offset}`;
          }
        }
      }
    }

    return sql;
  },

  /**
   * 分页查询
   * @param {string} table 表名
   * @param {string[]} fields 查询字段数组
   * @param {object} conditions 条件筛选
   * @param {number} page 当前页码
   * @param {number} pageSize 每页大小
   * @param {string} orderBy 排序字段(ASC升序 DESC降序)
   * @returns {{rowSql: string, countSql: string}} 分页查询SQL
   */
  paginate: (table, fields = ['*'], conditions = {}, page = 1, pageSize = 10, orderBy = 'id ASC') => {
    // 计算偏移量
    const offset = (Math.max(1, page) - 1) * Math.max(1, pageSize);
    
    // 构建查询数据的SQL
    const rowSql = MysqlQueryBuilder.select(
      table,
      fields,
      conditions,
      {
        limit: pageSize,
        offset: offset,
        orderBy: orderBy
      }
    );

    // 构建查询总数的SQL
    const safeTable = escapeIdentifier(table);
    const whereClause = Object.keys(conditions).length > 0 
      ? ` WHERE ${buildWhereClause(conditions)}`
      : '';
    
    const countSql = `SELECT COUNT(*) AS total FROM ${safeTable}${whereClause}`;

    return { rowSql, countSql };
  },

 /**
   * 联表查询
   * @param {string} mainTable 主表名
   * @param {Array} joinTables 联表配置数组 [{table:副表, alias：副表别名, on：连表条件, type：联表类型(INNER、LEFT、RIGHT 等)}]
   * @param {string[]} fields 查询字段数组
   * @param {object} conditions 条件对象(支持操作符)
   * @param {object} options 选项 {limit, offset, orderBy, groupBy}
   * @returns {string} 完整SQL查询语句
   */
 join: (mainTable, joinTables = [], fields = ['*'], conditions = {}, options = {}) => {
  // 安全处理主表名
  const safeMainTable = escapeIdentifier(mainTable);
  
  // 处理查询字段
  const safeFields = fields.map(f => {
    if (f === '*') return '*';
    
    // 处理带表别名的字段(如 user.name)
    if (f.includes('.')) {
      const [tableAlias, fieldName] = f.split('.');
      return `${escapeIdentifier(tableAlias)}.${escapeIdentifier(fieldName)}`;
    }
    
    return escapeIdentifier(f);
  }).join(', ');
  
  // 构建基础SQL
  let sql = `SELECT ${safeFields} FROM ${safeMainTable}`;

  // 添加JOIN子句
  joinTables.forEach(joinConfig => {
    const {
      table,
      alias = '',
      on,
      type = 'INNER'
    } = joinConfig;
    
    const joinType = ['LEFT', 'RIGHT', 'INNER', 'OUTER', 'FULL'].includes(type.toUpperCase()) 
      ? type.toUpperCase() 
      : 'INNER';
    
    const safeTable = escapeIdentifier(table);
    const safeAlias = alias ? ` AS ${escapeIdentifier(alias)}` : '';
    
    // 处理ON条件
    let onClause = '';
    if (typeof on === 'string') {
      // 简单字符串条件
      onClause = ` ON ${on}`;
    } else if (Array.isArray(on)) {
      // 数组形式 [['t1.id', '=', 't2.user_id'], 'AND', ['t1.status', '=', 1]]
      onClause = ` ON ${buildComplexCondition(on)}`;
    } else if (typeof on === 'object') {
      // 对象形式 { 't1.id': 't2.user_id' } 或 { 't1.id': { $eq: 't2.user_id' } }
      onClause = ` ON ${buildJoinOnClause(on)}`;
    } else {
      throw new Error('JOIN条件格式不正确');
    }
    
    sql += ` ${joinType} JOIN ${safeTable}${safeAlias}${onClause}`;
  });

  // 添加WHERE条件
  if (Object.keys(conditions).length > 0) {
    sql += ` WHERE ${buildWhereClause(conditions)}`;
  }

  // 添加其他选项(与select方法保持一致)
  if (options.groupBy) {
    sql += ` GROUP BY ${escapeIdentifierOrList(options.groupBy)}`;
  }
  if (options.orderBy) {
    sql += ` ORDER BY ${escapeOrderBy(options.orderBy)}`;
  }
  if (options.limit) {
    const limit = parseInt(options.limit, 10);
    if (!isNaN(limit) && limit > 0) {
      sql += ` LIMIT ${limit}`;
      if (options.offset) {
        const offset = parseInt(options.offset, 10);
        if (!isNaN(offset) && offset >= 0) {
          sql += ` OFFSET ${offset}`;
        }
      }
    }
  }

  return sql;
},


  /**
   * 插入数据
   * @param {string} table 表名
   * @param {object|object[]} data 要插入的数据对象或数组
   * @param {object} options 选项 {ignore, updateOnDuplicate}
   * @returns {string} 完整SQL插入语句
   */
  insert: (table, data, options = {}) => {
    const safeTable = escapeIdentifier(table);
    const isBulk = Array.isArray(data);
    const records = isBulk ? data : [data];

    if (records.length === 0) {
      throw new Error('插入数据不能为空');
    }

    // 获取并验证列名
    const columns = Object.keys(records[0]).map(escapeIdentifier);
    if (columns.length === 0) {
      throw new Error('插入数据必须包含至少一个字段');
    }

    // 构建VALUES部分
    const values = records.map(record =>
      `(${columns.map(col => formatValue(record[col.replace(/`/g, '')])).join(', ')})`
    ).join(', ');

    // 构建基础SQL
    let sql = `INSERT ${options.ignore ? 'IGNORE ' : ''}INTO ${safeTable} `;
    sql += `(${columns.join(', ')}) VALUES ${values}`;

    // 处理重复键更新
    if (options.updateOnDuplicate) {
      if (Array.isArray(options.updateOnDuplicate)) {
        sql += ` ON DUPLICATE KEY UPDATE ${
          options.updateOnDuplicate
            .map(col => `${escapeIdentifier(col)}=VALUES(${escapeIdentifier(col)})`)
            .join(', ')
        }`;
      } else if (typeof options.updateOnDuplicate === 'object') {
        sql += ` ON DUPLICATE KEY UPDATE ${
          Object.entries(options.updateOnDuplicate)
            .map(([key, val]) => `${escapeIdentifier(key)}=${formatValue(val)}`)
            .join(', ')
        }`;
      }
    }

    return sql;
  },

  /**
   * 更新数据
   * @param {string} table 表名
   * @param {object} data 要更新的数据对象
   * @param {object} conditions 条件对象
   * @param {object} options 选项 {limit, orderBy}
   * @returns {string} 完整SQL更新语句
   */
  update: (table, data, conditions = {}, options = {}) => {
    const safeTable = escapeIdentifier(table);
    
    // 构建SET子句
    const setClause = Object.entries(data)
      .map(([key, value]) => `${escapeIdentifier(key)} = ${formatValue(value)}`)
      .join(', ');
    
    if (!setClause) {
      throw new Error('更新数据不能为空');
    }

    let sql = `UPDATE ${safeTable} SET ${setClause}`;

    // 添加WHERE条件
    if (Object.keys(conditions).length > 0) {
      sql += ` WHERE ${buildWhereClause(conditions)}`;
    }

    // 添加其他选项
    if (options.orderBy) {
      sql += ` ORDER BY ${escapeOrderBy(options.orderBy)}`;
    }
    if (options.limit) {
      const limit = parseInt(options.limit, 10);
      if (!isNaN(limit) && limit > 0) {
        sql += ` LIMIT ${limit}`;
      }
    }

    return sql;
  },

  /**
   * 删除数据
   * @param {string} table 表名
   * @param {object} conditions 条件对象
   * @param {object} options 选项 {limit, orderBy}
   * @returns {string} 完整SQL删除语句
   */
  delete: (table, conditions = {}, options = {}) => {
    const safeTable = escapeIdentifier(table);
    let sql = `DELETE FROM ${safeTable}`;

    // 添加WHERE条件
    if (Object.keys(conditions).length > 0) {
      sql += ` WHERE ${buildWhereClause(conditions)}`;
    }

    // 添加其他选项
    if (options.orderBy) {
      sql += ` ORDER BY ${escapeOrderBy(options.orderBy)}`;
    }
    if (options.limit) {
      const limit = parseInt(options.limit, 10);
      if (!isNaN(limit) && limit > 0) {
        sql += ` LIMIT ${limit}`;
      }
    }

    return sql;
  },

  /**
   * 级联删除(需要数据库支持外键约束)
   * @param {string} mainTable 主表名
   * @param {object} conditions 主表删除条件
   * @param {Array} cascadeTables 级联表配置数组 [{table, condition}]
   * @param {object} options 选项 {transaction: true}
   * @returns {string[]} SQL语句数组(按执行顺序)
   */
  cascadeDelete: (mainTable, conditions = {}, cascadeTables = [], options = {}) => {
    const sqlStatements = [];
    
    // 1. 首先处理级联表的删除
    cascadeTables.forEach(cascadeConfig => {
      const { table, condition } = cascadeConfig;
      
      if (!table) {
        throw new Error('级联表配置必须包含table属性');
      }
      
      const safeTable = escapeIdentifier(table);
      let whereClause = '';
      
      // 构建WHERE条件
      if (condition) {
        if (typeof condition === 'string') {
          whereClause = ` WHERE ${condition}`;
        } else if (typeof condition === 'object') {
          whereClause = ` WHERE ${buildWhereClause(condition)}`;
        } else {
          throw new Error('级联表条件格式不正确');
        }
      }
      
      sqlStatements.push(`DELETE FROM ${safeTable}${whereClause}`);
    });
    
    // 2. 最后删除主表记录
    const safeMainTable = escapeIdentifier(mainTable);
    let whereClause = '';
    
    if (Object.keys(conditions).length > 0) {
      whereClause = ` WHERE ${buildWhereClause(conditions)}`;
    }
    
    sqlStatements.push(`DELETE FROM ${safeMainTable}${whereClause}`);
    
    // 如果需要事务包装
    if (options.transaction) {
      return [
        'START TRANSACTION;',
        ...sqlStatements,
        'COMMIT;'
      ];
    }
    
    return sqlStatements;
  }

};
// ========== 辅助函数 ==========

/**
 * 构建WHERE子句
 * @param {object} conditions 条件对象
 * @returns {string} WHERE子句
 */
function buildWhereClause(conditions) {
  return Object.entries(conditions)
    .map(([key, value]) => {
      const safeKey = escapeIdentifier(key);
      
      // 处理NULL值
      if (value === null || value === undefined) {
        return `${safeKey} IS NULL`;
      }
      
      // 处理数组(IN条件)
      if (Array.isArray(value)) {
        if (value.length === 0) return '0=1'; // 空数组返回永假条件
        return `${safeKey} IN (${value.map(v => formatValue(v)).join(', ')})`;
      }
      
      // 处理对象(操作符)
      if (typeof value === 'object' && !(value instanceof Date)) {
        const operators = {
          $gt: '>', $gte: '>=', $lt: '<', $lte: '<=', 
          $ne: '!=', $eq: '=', $like: 'LIKE', $notLike: 'NOT LIKE',
          $in: 'IN', $notIn: 'NOT IN', $between: 'BETWEEN'
        };
        
        return Object.entries(value)
          .map(([op, val]) => {
            if (op === '$between' && Array.isArray(val) && val.length === 2) {
              return `${safeKey} BETWEEN ${formatValue(val[0])} AND ${formatValue(val[1])}`;
            }
            if (op === '$in' || op === '$notIn') {
              if (!Array.isArray(val)) return '';
              if (val.length === 0) return op === '$in' ? '0=1' : '1=1';
              return `${safeKey} ${operators[op]} (${val.map(v => formatValue(v)).join(', ')})`;
            }
            if (operators[op]) {
              return `${safeKey} ${operators[op]} ${formatValue(val)}`;
            }
            return '';
          })
          .filter(Boolean)
          .join(' AND ');
      }
      
      // 处理普通值
      return `${safeKey} = ${formatValue(value)}`;
    })
    .filter(Boolean)
    .join(' AND ');
}

/**
 * 格式化值
 * @param {*} value 要格式化的值
 * @returns {string} SQL安全的值表示
 */
function formatValue(value) {
  if (value === null || value === undefined || value === '') {
    return 'NULL';
  }
  
  // 处理数字类型(包括大整数)
  if (typeof value === 'number') {
    // 大整数转为字符串避免精度丢失
    if (!Number.isSafeInteger(value)) {
      return `'${value}'`;
    }
    return value.toString();
  }
  
  // 处理布尔值
  if (typeof value === 'boolean') {
    return value ? '1' : '0';
  }
  
  // 处理日期
  if (value instanceof Date) {
    return `'${value.toISOString().slice(0, 19).replace('T', ' ')}'`;
  }
  
  // 处理字符串(转义单引号)
  if (typeof value === 'string') {
    return `'${escapeString(value)}'`;
  }
  
  // 其他类型转为字符串
  return `'${escapeString(String(value))}'`;
}

/**
 * 转义字符串
 * @param {string} str 要转义的字符串
 * @returns {string} 转义后的字符串
 */
function escapeString(str) {
  return str.replace(/[\0\n\r\b\t\\'"\x1a]/g, function (char) {
    switch (char) {
      case '\0': return '\\0';
      case '\n': return '\\n';
      case '\r': return '\\r';
      case '\b': return '\\b';
      case '\t': return '\\t';
      case '\x1a': return '\\Z';
      case '\'': return '\\\'';
      case '"': return '\\"';
      case '\\': return '\\\\';
      default: return char;
    }
  });
}

/**
 * 转义标识符(表名/列名)
 * @param {string} identifier 标识符
 * @returns {string} 转义后的标识符
 */
function escapeIdentifier(identifier) {
  if (typeof identifier !== 'string') {
    throw new Error('标识符必须是字符串');
  }
  return `\`${identifier.replace(/`/g, '``')}\``;
}

/**
 * 转义标识符列表(groupBy用)
 * @param {string} list 逗号分隔的列表
 * @returns {string} 转义后的列表
 */
function escapeIdentifierOrList(list) {
  return list.split(',')
    .map(item => escapeIdentifier(item.trim()))
    .join(', ');
}

/**
 * 转义ORDER BY子句
 * @param {string} orderBy 排序子句
 * @returns {string} 转义后的排序子句
 */
function escapeOrderBy(orderBy) {
  return orderBy.split(',')
    .map(part => {
      const [field, direction] = part.trim().split(/\s+/);
      const safeField = escapeIdentifier(field);
      const safeDirection = direction 
        ? direction.toUpperCase() === 'DESC' ? 'DESC' : 'ASC'
        : '';
      return safeDirection ? `${safeField} ${safeDirection}` : safeField;
    })
    .join(', ');
}
/**
 * 构建JOIN ON子句(处理对象形式的ON条件)
 * @param {object} onConditions ON条件对象
 * @returns {string} ON子句
 */
function buildJoinOnClause(onConditions) {
  return Object.entries(onConditions)
    .map(([left, right]) => {
      // 左表字段
      const leftParts = left.split('.');
      const leftField = leftParts.length > 1 
        ? `${escapeIdentifier(leftParts[0])}.${escapeIdentifier(leftParts[1])}`
        : escapeIdentifier(left);
      
      // 处理右表值(可能是字段名或值)
      let rightValue;
      if (typeof right === 'object' && !Array.isArray(right)) {
        // 操作符对象 { $eq: 't2.id' }
        const [op, val] = Object.entries(right)[0];
        const operator = {
          $eq: '=', $ne: '!=', $gt: '>', $gte: '>=', $lt: '<', $lte: '<='
        }[op] || '=';
        
        rightValue = typeof val === 'string' && val.includes('.')
          ? `${escapeIdentifier(val.split('.')[0])}.${escapeIdentifier(val.split('.')[1])}`
          : formatValue(val);
        
        return `${leftField} ${operator} ${rightValue}`;
      } else {
        // 简单字段名或值
        rightValue = typeof right === 'string' && right.includes('.')
          ? `${escapeIdentifier(right.split('.')[0])}.${escapeIdentifier(right.split('.')[1])}`
          : formatValue(right);
        
        return `${leftField} = ${rightValue}`;
      }
    })
    .join(' AND ');
}

/**
 * 构建复杂条件(处理数组形式的条件)
 * @param {Array} conditions 条件数组
 * @returns {string} 条件子句
 */
function buildComplexCondition(conditions) {
  return conditions.map(item => {
    if (Array.isArray(item)) {
      // 条件数组 [field, operator, value]
      const [field, operator, value] = item;
      const safeField = field.includes('.')
        ? `${escapeIdentifier(field.split('.')[0])}.${escapeIdentifier(field.split('.')[1])}`
        : escapeIdentifier(field);
      
      const safeOperator = ['=', '!=', '>', '<', '>=', '<=', 'LIKE', 'NOT LIKE', 'IN', 'NOT IN'].includes(operator.toUpperCase())
        ? operator.toUpperCase()
        : '=';
      
      return `${safeField} ${safeOperator} ${formatValue(value)}`;
    } else if (['AND', 'OR'].includes(item.toUpperCase())) {
      // 逻辑运算符
      return item.toUpperCase();
    } else {
      // 其他情况直接返回(可能是已经处理好的条件字符串)
      return item;
    }
  }).join(' ');
}